﻿<%@ WebHandler Language="C#" Class="PZTouLiaoDashBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;

/*
 * Description: 盘轴分厂投料统计看板
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.10.11
 */

public class PZTouLiaoDashBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getEmployeeCompleteInfo":
                        r = getEmployeeCompleteInfo(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    Result getData(JObject jObject)
    {
        string sql = @"
SELECT t.productname,t.productdescription,t.containerno,SUM(t.qty) qty,
       MAX(t.factorystartdate) factorystartdate,
       CASE WHEN MAX(t.gongyiWaitTime) < 0 THEN 0 ELSE MAX(t.gongyiWaitTime) END gongyiWaitTime,
       CASE WHEN MAX(t.faliaoWaitTime) < 0 THEN 0 ELSE MAX(t.faliaoWaitTime) END faliaoWaitTime,
       CASE WHEN MAX(t.daoliaoWaitTime) < 0 THEN 0 ELSE MAX(t.daoliaoWaitTime) END daoliaoWaitTime
FROM (
      SELECT cci.productname,cci.productdescription,cci.containerno,cci.containername,cci.qty,c.factorystartdate,
            cci.techniquedate,ua.attributevalue,cci.materialdate,
            CASE WHEN cci.techniquedate IS NULL THEN TRUNC((SYSDATE - c.factorystartdate),2)
                 ELSE TRUNC((cci.techniquedate - c.factorystartdate),2) END gongyiWaitTime,
            CASE WHEN cci.techniquedate IS NULL THEN 0
                 WHEN cci.techniquedate IS NOT NULL AND ua.attributevalue IS NOT NULL THEN TRUNC((to_date(ua.attributevalue,'yyyy/mm/dd hh24:mi:ss')-cci.techniquedate),2)
                 WHEN cci.techniquedate IS NOT NULL AND ua.attributevalue IS NULL THEN TRUNC((SYSDATE - cci.techniquedate),2) 
            END faliaoWaitTime,
            CASE WHEN ua.attributevalue IS NULL THEN 0
                 WHEN ua.attributevalue IS NOT NULL AND cci.materialdate IS NOT NULL THEN TRUNC((cci.materialdate-to_date(ua.attributevalue,'yyyy/mm/dd hh24:mi:ss')),2)
                 WHEN ua.attributevalue IS NOT NULL AND cci.materialdate IS NULL THEN TRUNC((Sysdate-to_date(ua.attributevalue,'yyyy/mm/dd hh24:mi:ss')),2) 
            END daoliaoWaitTime
      FROM containercurrentinfo cci
      LEFT JOIN container c ON cci.containerid = c.containerid
      LEFT JOIN Userattribute ua ON ua.parentid = c.containerid AND ua.userattributename='发料时间'
      WHERE cci.containerstatus = 1 AND cci.isinstore = -1 ";
        if (jObject["factoryid"] != null)
        {
            sql += string.Format(" AND c.originalfactoryid = '{0}'", jObject["factoryid"]);
        }
        sql += @"
) t
GROUP BY t.productname,t.productdescription,t.containerno
";
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getEmployeeCompleteInfo(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT t.fullname,t.productname,t.productdescription,t.containerno,SUM(t.qty) qty,
       MAX(t.factorystartdate) factorystartdate,
       CASE WHEN MAX(t.gongyiWaitTime) < 0 THEN 0 ELSE MAX(t.gongyiWaitTime) END gongyiWaitTime,
       CASE WHEN MAX(t.faliaoWaitTime) < 0 THEN 0 ELSE MAX(t.faliaoWaitTime) END faliaoWaitTime,
       CASE WHEN MAX(t.daoliaoWaitTime) < 0 THEN 0 ELSE MAX(t.daoliaoWaitTime) END daoliaoWaitTime,SUM(taskCount) taskCount
FROM (
         SELECT e.fullname,cci.productname,cci.productdescription,cci.containerno,cci.containername,cci.qty,c.factorystartdate,
                cci.techniquedate,ua.attributevalue,cci.materialdate,
                CASE WHEN cci.techniquedate IS NULL AND TRUNC((SYSDATE - c.factorystartdate),2) < 1 THEN 1 ELSE 0 END taskCount,
                CASE WHEN cci.techniquedate IS NULL THEN TRUNC((SYSDATE - c.factorystartdate),2)
                     ELSE TRUNC((cci.techniquedate - c.factorystartdate),2) END gongyiWaitTime,
                CASE WHEN cci.techniquedate IS NULL THEN 0
                     WHEN cci.techniquedate IS NOT NULL AND ua.attributevalue IS NOT NULL THEN TRUNC((to_date(ua.attributevalue,'yyyy/mm/dd hh24:mi:ss')-cci.techniquedate),2)
                     WHEN cci.techniquedate IS NOT NULL AND ua.attributevalue IS NULL THEN TRUNC((SYSDATE - cci.techniquedate),2) END faliaoWaitTime,
                CASE WHEN ua.attributevalue IS NULL THEN 0
                     WHEN ua.attributevalue IS NOT NULL AND cci.materialdate IS NOT NULL THEN TRUNC((cci.materialdate-to_date(ua.attributevalue,'yyyy/mm/dd hh24:mi:ss')),2)
                     WHEN ua.attributevalue IS NOT NULL AND cci.materialdate IS NULL THEN TRUNC((Sysdate-to_date(ua.attributevalue,'yyyy/mm/dd hh24:mi:ss')),2) end daoliaoWaitTime
         FROM containercurrentinfo cci
         LEFT JOIN container c ON cci.containerid = c.containerid
         LEFT JOIN Userattribute ua ON ua.parentid = c.containerid AND ua.userattributename='发料时间'
         LEFT JOIN employee e ON e.employeeid = c.startemployeeid
         WHERE cci.containerstatus = 1 AND cci.isinstore = -1 ";

        if (jObject["factoryid"] != null)
        {
            sql += string.Format(" AND c.originalfactoryid = '{0}'", jObject["factoryid"]);
        }

        sql += @"
) t
GROUP BY t.fullname,t.productname,t.productdescription,t.containerno
";
        DataTable dtQueryData = dal.GetDataTable(sql);
        if (dtQueryData.Rows.Count == 0)
        {
            r.data = new DataTable();
            r.total = Convert.ToInt32(0);
            r.meta = new Result.Meta(200, "请求成功");
            return r;
        }

        DataTable dtResult = dtQueryData.DefaultView.ToTable(true, "fullname");
        dtResult.Columns.Add("inTimeJiaoDuiQty");// 及时校对数
        dtResult.Columns.Add("delayJiaoDuiQty");// 超期校对数
        dtResult.Columns.Add("veryDelayJiaoDuiQty");// 严重超期校对数
        dtResult.Columns.Add("inTimeFaLiaoQty");// 及时发料数
        dtResult.Columns.Add("delayFaLiaoQty");// 超期发料数
        dtResult.Columns.Add("veryDelayFaLiaoQty");// 严重超期发料
        dtResult.Columns.Add("inTimeDaoLiaoQty");// 及时到料数
        dtResult.Columns.Add("delayDaoLiaoQty");// 超期到料数
        dtResult.Columns.Add("vertDelayDaoLiaoQty");// 严重供超期到料数
        dtResult.Columns.Add("taskCount");// 待校对

        foreach (DataRow dr in dtResult.Rows)
        {
            string inTimeJiaoDuiQtyFilter = string.Format(" fullname = '{0}' and gongyiWaitTime < 1", dr["fullname"]);
            string delayJiaoDuiQtyFilter = string.Format(" fullname = '{0}' and gongyiWaitTime >= 1 and gongyiWaitTime <= 3 ", dr["fullname"]);
            string veryDelayJiaoDuiQtyFilter = string.Format(" fullname = '{0}' and gongyiWaitTime >3 ", dr["fullname"]);

            string inTimeFaLiaoQtyFilter = string.Format(" fullname = '{0}' and faliaoWaitTime < 7", dr["fullname"]);
            string delayFaLiaoQtyFilter = string.Format(" fullname = '{0}' and faliaoWaitTime >= 7 and faliaoWaitTime <= 14 ", dr["fullname"]);
            string veryDelayFaLiaoQtyFilter = string.Format(" fullname = '{0}' and faliaoWaitTime > 14 ", dr["fullname"]);

            string inTimeDaoLiaoQtyFilter = string.Format(" fullname = '{0}' and daoliaoWaitTime < 1", dr["fullname"]);
            string delayDaoLiaoQtyFilter = string.Format(" fullname = '{0}' and daoliaoWaitTime >= 1 and daoliaoWaitTime <= 2 ", dr["fullname"]);
            string vertDelayDaoLiaoQtyFilter = string.Format(" fullname = '{0}' and daoliaoWaitTime > 2 ", dr["fullname"]);

            string taskCountFilter = string.Format(" fullname = '{0}' and taskCount <> 0 ", dr["fullname"]);

            dr["inTimeJiaoDuiQty"] = Convert.ToInt16(dtQueryData.Compute("Count(fullname)", inTimeJiaoDuiQtyFilter));
            dr["delayJiaoDuiQty"] = Convert.ToInt16(dtQueryData.Compute("Count(fullname)", delayJiaoDuiQtyFilter));
            dr["veryDelayJiaoDuiQty"] = Convert.ToInt16(dtQueryData.Compute("Count(fullname)", veryDelayJiaoDuiQtyFilter));

            dr["inTimeFaLiaoQty"] = Convert.ToInt16(dtQueryData.Compute("Count(fullname)", inTimeFaLiaoQtyFilter));
            dr["delayFaLiaoQty"] = Convert.ToInt16(dtQueryData.Compute("Count(fullname)", delayFaLiaoQtyFilter));
            dr["veryDelayFaLiaoQty"] = Convert.ToInt16(dtQueryData.Compute("Count(fullname)", veryDelayFaLiaoQtyFilter));

            dr["inTimeDaoLiaoQty"] = Convert.ToInt16(dtQueryData.Compute("Count(fullname)", inTimeDaoLiaoQtyFilter));
            dr["delayDaoLiaoQty"] = Convert.ToInt16(dtQueryData.Compute("Count(fullname)", delayDaoLiaoQtyFilter));
            dr["vertDelayDaoLiaoQty"] = Convert.ToInt16(dtQueryData.Compute("Count(fullname)", vertDelayDaoLiaoQtyFilter));

            dr["taskCount"] = Convert.ToInt16(dtQueryData.Compute("Count(fullname)", taskCountFilter));
        }

        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }
}